


local file_path_in "/tochange/"

set seed 59865814

use "`file_path_in'//county transactions/listings_newhomes.dta", clear

sort CMAS_PARCEL_ID ListDate

drop ListingStatus  
rename CMAS_FULLSITEADDRESSUNPARSED1 address
drop if CMAS_PARCEL_ID==""
rename CMAS_FIPS_CODE fips_code 

drop if ClosePrice==.

duplicates tag ParcelNumber ListDate CloseDate ListPrice ClosePrice ListingStatusCode, gen(tag)
drop if tag>0
drop tag

* ListDate
g listyear = substr(ListDate,1,4)
g listmonth = substr(ListDate, 6,2)
g listday = substr(ListDate,9,2)
g list_dt = listyear + listmonth + listday  /* to match RECORDINGDATE format */
destring listyear, replace
destring listmonth, replace
destring listday, replace
g listdate = mdy(listmonth,listday,listyear)
format listdate %td
gen list_month = mofd(listdate)
format list_month %tm
drop  listyear listmonth listday 

*CloseDate
g closeyear = substr(CloseDate,1,4)
g closemonth = substr(CloseDate, 6,2)
g closeday = substr(CloseDate,9,2)
g close_dt = closeyear + closemonth + closeday  /* to match RECORDINGDATE format */

destring closeyear, replace
destring closemonth, replace
destring closeday, replace

g closedate = mdy(closemonth,closeday,closeyear)
format closedate %td
gen close_month = mofd(closedate)
format close_month %tm
drop  closeyear closemonth closeday 

* Time on market (days)
g time_mkt = closedate - listdate

** filters **
keep if closedate!=. & ClosePrice!=. /* only look at sales */

** keep sales only 
keep if ListingStatusCode=="Closed Sale" | ListingStatusCode=="Closed" | ListingStatusCode=="Sold"  | ListingStatusCode=="" | ListingStatusCode=="SOLD" | ListingStatusCode== "SOLD-INNER OFFICE"

** get rid of duplicate listings (usually where close date differs by just one day, or where ListingStatusCode says "Closed" and then "Sale"
set sortseed 59865814  
bysort CMAS_PARCEL_ID listdate  address ClosePrice: gen count = _n
keep if count==1
	
order  CMAS_PARCEL_ID listdate  closedate  time_mkt ListPrice ClosePrice ListingStatusCode address CMAS_Zip5 

format CMAS_PARCEL_ID %30s
format ParcelNumber %30s
drop if CMAS_PARCEL_ID ==""

sort CMAS_PARCEL_ID listdate

** kick out dataset for analysis of elasticity of time to sale to price by city:
save "`file_path_in'//county transactions/elasticity_cleaned.dta", replace

** kick out dataset for listing times by builder shock analysis, to merge to deeds:
keep CMAS_PARCEL_ID listdate ListDate list_month closedate close_dt CloseDate close_month time_mkt ListPrice ClosePrice ListingStatusCode list_dt StatusChangeDate address CMAS_Zip5 CMAS_PARCEL_SEQ_NBR ListingID OriginalListPrice RentSaleLease YearBuilt CMAS_PROPERTY_CITY_1 CMAS_PROPERTY_STATE_1 fips_code

save "`file_path_in'//county transactions/listings_newhomes_cleaned.dta", replace




















